CREATE TABLE DATA_METADATA_TABLE_STRUCTURE (
    ID VARCHAR(64) NOT NULL,
    COLLECTION_ID VARCHAR(64),
    EXECUTION_ID VARCHAR(64),
    TABLE_NAME VARCHAR(128),
    TABLE_TYPE VARCHAR(32),
    TABLE_COMMENT VARCHAR(512),
    CREATE_SQL CLOB,
    CREATE_TIME TIMESTAMP DEFAULT SYSDATE,
    UPDATE_TIME TIMESTAMP DEFAULT SYSDATE,
    VERSION INTEGER,
    IS_LATEST INTEGER DEFAULT 1,
    IS_DELETED INTEGER DEFAULT 0,
    CONSTRAINT PK_DATA_METADATA_TABLE_STRUCTURE PRIMARY KEY (ID)
);

CREATE INDEX IDX_TABLE_STRUCTURE_COLLECTION_ID ON DATA_METADATA_TABLE_STRUCTURE (COLLECTION_ID);
CREATE INDEX IDX_TABLE_STRUCTURE_EXECUTION_ID ON DATA_METADATA_TABLE_STRUCTURE (EXECUTION_ID);
CREATE INDEX IDX_TABLE_STRUCTURE_TABLE_NAME ON DATA_METADATA_TABLE_STRUCTURE (TABLE_NAME);
CREATE INDEX IDX_TABLE_STRUCTURE_VERSION ON DATA_METADATA_TABLE_STRUCTURE (VERSION);
CREATE INDEX IDX_TABLE_STRUCTURE_IS_LATEST ON DATA_METADATA_TABLE_STRUCTURE (IS_LATEST);
CREATE INDEX IDX_TABLE_STRUCTURE_IS_DELETED ON DATA_METADATA_TABLE_STRUCTURE (IS_DELETED);

ALTER TABLE DATA_METADATA_TABLE_STRUCTURE ADD CONSTRAINT CK_TABLE_STRUCTURE_IS_DELETED CHECK (IS_DELETED IN (0, 1));
ALTER TABLE DATA_METADATA_TABLE_STRUCTURE ADD CONSTRAINT CK_TABLE_STRUCTURE_IS_LATEST CHECK (IS_LATEST IN (0, 1));

CREATE SEQUENCE SEQ_METADATA_TABLE_STRUCTURE_ID
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

CREATE OR REPLACE TRIGGER TRG_METADATA_TABLE_STRUCTURE_ID
BEFORE INSERT ON DATA_METADATA_TABLE_STRUCTURE
FOR EACH ROW
BEGIN
    IF :NEW.ID IS NULL THEN
        SELECT 'TS' || TO_CHAR(SYSDATE, 'YYYYMMDD') || LPAD(SEQ_METADATA_TABLE_STRUCTURE_ID.NEXTVAL, 6, '0')
        INTO :NEW.ID
        FROM DUAL;
    END IF;
END;
/

CREATE OR REPLACE TRIGGER TRG_METADATA_TABLE_STRUCTURE_UPDATE
BEFORE UPDATE ON DATA_METADATA_TABLE_STRUCTURE
FOR EACH ROW
BEGIN
    :NEW.UPDATE_TIME := SYSDATE;
END;
/

COMMENT ON TABLE DATA_METADATA_TABLE_STRUCTURE IS '元数据表结构信息表';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.ID IS '主键ID';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.COLLECTION_ID IS '采集任务ID';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.EXECUTION_ID IS '执行ID';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.TABLE_NAME IS '表名';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.TABLE_TYPE IS '表类型';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.TABLE_COMMENT IS '表注释';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.CREATE_SQL IS '建表SQL';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.VERSION IS '版本号';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.IS_LATEST IS '是否最新版本（1是，0否）';
COMMENT ON COLUMN DATA_METADATA_TABLE_STRUCTURE.IS_DELETED IS '是否删除（0-未删除，1-已删除）';

CREATE OR REPLACE VIEW V_METADATA_TABLE_STRUCTURE_LATEST AS
SELECT *
FROM DATA_METADATA_TABLE_STRUCTURE
WHERE IS_LATEST = 1 AND IS_DELETED = 0;

CREATE OR REPLACE PROCEDURE PROC_UPDATE_TABLE_STRUCTURE_VERSION(
    p_collection_id IN VARCHAR2,
    p_execution_id IN VARCHAR2
) AS
BEGIN
    UPDATE DATA_METADATA_TABLE_STRUCTURE
    SET IS_LATEST = 0
    WHERE COLLECTION_ID = p_collection_id
    AND EXECUTION_ID <> p_execution_id
    AND IS_LATEST = 1;
    
    COMMIT;
END;
/

CREATE OR REPLACE FUNCTION FUNC_GET_TABLE_STRUCTURE_MAX_VERSION(
    p_collection_id IN VARCHAR2
) RETURN NUMBER AS
    v_max_version NUMBER;
BEGIN
    SELECT COALESCE(MAX(VERSION), 0)
    INTO v_max_version
    FROM DATA_METADATA_TABLE_STRUCTURE
    WHERE COLLECTION_ID = p_collection_id
    AND IS_DELETED = 0;
    
    RETURN v_max_version;
END;
/ 